﻿using System;
using System.Collections.Generic;
using System.Text;
using MySql.Data.MySqlClient;
using BBMS.DAL.IDAL;
using BBMS.BLL.DataContract;
using System.Data;
using BBMS.DAL.MySQLHelper;

namespace BBMS.DAL.MySQL
{
    public class Param : IParam
    {
        private MySqlConnection _internalConnection;
        private MySqlTransaction _internalADOTransaction = null;

        public void Open(string connString)
        {
            if (_internalConnection == null)
                _internalConnection = new MySqlConnection(connString);
            if (_internalConnection.State != ConnectionState.Open)
                _internalConnection.Open();
        }
        public void Close()
        {
            if (_internalConnection != null && _internalConnection.State != System.Data.ConnectionState.Closed)
                _internalConnection.Close();
        }

        #region SQL

        private const string INSERT_PARM = "INSERT INTO params(code, name, content) VALUES (@code, @name, @content) ON DUPLICATE KEY UPDATE name=@name, content=@content";

        private const string GET_ALLPARAM = "SELECT * FROM params";

        private const string GET_PARAM_BYCODE = "SELECT * FROM params WHERE code=@code";

        private const string GET_PARAM_BYNAME = "SELECT * FROM params WHERE name=@name";

        #endregion

        #region Param

        private const string PARM_CODE = "@CODE";
        private const string PARM_NAME = "@NAME";
        private const string PARM_CONTENT = "@CONTENT";

        #endregion


        #region IParam Members

        public void insertParm(ParamData item)
        {
            try
            {
                MySqlParameter[] parms = new MySqlParameter[] { 
                    new MySqlParameter( PARM_CODE, MySqlDbType.VarChar),
                    new MySqlParameter( PARM_NAME, MySqlDbType.VarChar),
                    new MySqlParameter( PARM_CONTENT, MySqlDbType.VarChar)
                };

                parms[0].Value = item.Code;
                parms[1].Value = item.Name;
                parms[2].Value = item.Content;

                MyHelper.ExecuteNonQuery(_internalConnection, _internalADOTransaction, CommandType.Text, INSERT_PARM, parms);
            }
            catch (System.Exception ex)
            {
                throw ex;
            }
        }

        public List<ParamData> getAllParam()
        {
            try
            {
                List<ParamData> list = new List<ParamData>();

                MySqlDataReader rdr = MyHelper.ExecuteReaderNoParm(_internalConnection, _internalADOTransaction, CommandType.Text, GET_ALLPARAM);

                while (rdr.Read())
                {
                    list.Add(new ParamData(rdr["CODE"].ToString(),
                                            rdr["NAME"].ToString(),
                                            rdr["CONTENT"].ToString()
                                           ));
                }

                rdr.Close();
                return list;
            }
            catch (System.Exception e)
            {
                throw e;
            }
        }

        public ParamData getParamByCode(string code)
        {
            MySqlDataReader rdr = null;
            try
            {
                MySqlParameter parm1 = new MySqlParameter(PARM_CODE, MySqlDbType.VarChar);
                parm1.Value = code;
                ParamData item = new ParamData();
                rdr = MyHelper.ExecuteReaderSingleParm(_internalConnection, _internalADOTransaction, CommandType.Text, GET_PARAM_BYCODE, parm1);

                if (rdr.HasRows)
                {
                    rdr.Read();
                    item.Code = rdr["CODE"].ToString();
                    item.Name = rdr["NAME"].ToString();
                    item.Content = rdr["CONTENT"].ToString();
                }
                return item;
            }
            catch (System.Exception e)
            {
                throw e;
            }
            finally
            {
                rdr.Close();
            }
        }

        public ParamData getParamByName(string name)
        {
            MySqlDataReader rdr = null;
            try
            {
                MySqlParameter parm1 = new MySqlParameter(PARM_CODE, MySqlDbType.VarChar);
                parm1.Value = name;
                ParamData item = new ParamData();
                rdr = MyHelper.ExecuteReaderSingleParm(_internalConnection, _internalADOTransaction, CommandType.Text, GET_PARAM_BYNAME, parm1);

                if (rdr.HasRows)
                {
                    rdr.Read();
                    item.Code = rdr["CODE"].ToString();
                    item.Name = rdr["NAME"].ToString();
                    item.Content = rdr["CONTENT"].ToString();
                }
                return item;
            }
            catch (System.Exception e)
            {
                throw e;
            }
            finally
            {
                rdr.Close();
            }
        }

        #endregion
    }
}
